﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Pms.Utils;
using System.Data.OracleClient;
using Biblioteca.Utils;
using Biblioteca.DataBase;

namespace SERIX.Classes
{
    public class CBensPropriedades
    {
        private string IDT_BENS_PROP;
        private string IDT_PROP;
        private string IDT_DESC;
        private string QUANT_PROP;
        private string ANO_PROP;
        private string TPO_VINC;
        private string BENS_PROP;
        private string DATA_FIM;
        private string USER_FIM;
        private string QUANT2_PROP;
        private string QUANT3_PROP;
        private string IDT_EVOLUCAO;


        public CBensPropriedades()
        {

        }

        public CBensPropriedades( string IDT_BENS_PROP, string IDT_PROP, string IDT_DESC, string QUANT_PROP, string ANO_PROP, string TPO_VINC, string BENS_PROP, string DATA_FIM, string USER_FIM, string QUANT2_PROP, string QUANT3_PROP)
        {
            this.IDT_BENS_PROP = IDT_BENS_PROP;
            this.IDT_PROP = IDT_PROP;
            this.IDT_DESC = IDT_DESC;
            this.QUANT_PROP = QUANT_PROP;
            this.ANO_PROP = ANO_PROP;
            this.TPO_VINC = TPO_VINC;
            this.BENS_PROP = BENS_PROP;
            this.DATA_FIM = DATA_FIM;
            this.USER_FIM = USER_FIM;
            this.QUANT2_PROP = QUANT2_PROP;
            this.QUANT3_PROP = QUANT3_PROP;
        }

        #region PROPRIEDADES
        public string Idt_Evolucao
        {
            get { return this.IDT_EVOLUCAO; }
            set { this.IDT_EVOLUCAO = value; }
        }
        /// <summary>
        /// Identificador da tabela Bens
        /// </summary>
        public string Idt_Bens_prop
        {
            get
            {
                return this.IDT_BENS_PROP;
            }
            set
            {
                this.IDT_BENS_PROP = value;
            }
        }

        /// <summary>
        /// Identificador do Beneficiario
        /// </summary>
        public string Idt_prop
        {
            get
            {
                return this.IDT_PROP;
            }
            set
            {
                this.IDT_PROP = value;
            }
        }

        /// <summary>
        /// Identificador da Descrição do bem
        /// </summary>
        public string Idt_desc
        {
            get
            {
                return this.IDT_DESC;
            }
            set
            {
                this.IDT_DESC = value;
            }
        }

        /// <summary>
        /// Quantidade do Bem
        /// </summary>
        public string Quant_prop
        {
            get
            {
                return this.QUANT_PROP;
            }
            set
            {
                this.QUANT_PROP = value;
            }
        }

        public string Quant2_prop
        {
            get
            {
                return this.QUANT2_PROP;
            }
            set
            {
                this.QUANT2_PROP = value;
            }
        }

        public string Quant3_prop
        {
            get
            {
                return this.QUANT3_PROP;
            }
            set
            {
                this.QUANT3_PROP = value;
            }
        }

        /// <summary>
        /// Ano de inserção do bem
        /// </summary>
        public string Ano_prop
        {
            get
            {
                return this.ANO_PROP;
            }
            set
            {
                this.ANO_PROP = value;
            }
        }

        /// <summary>
        /// Tipo do Vinculo do Bem
        /// </summary>
        public string Tpo_vinc
        {
            get
            {
                return this.TPO_VINC;
            }
            set
            {
                this.TPO_VINC = value;
            }
        }

        public string Bens_prop
        {
            get
            {
                return this.BENS_PROP;
            }
            set
            {
                this.BENS_PROP = value;
            }
        }

        /// <summary>
        /// Data de ultima alteração
        /// </summary>
        public string Data_fim
        {
            get
            {
                return this.DATA_FIM;
            }
            set
            {
                this.DATA_FIM = value;
            }
        }

        /// <summary>
        /// Usuario que fez a ultima alteração
        /// </summary>
        public string User_fim
        {
            get
            {
                return this.USER_FIM;
            }
            set
            {
                this.USER_FIM = value;
            }
        }
        #endregion


        public void Incluir(string Login, bool Evolucao)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            string _EVOL = "";
            string _CAMPO = "";
            string _CAMPO2 = "";
            if (Evolucao)
            {
                _EVOL = "_EVOL";
                _CAMPO = "IDT_EVOLUCAO, ";
                _CAMPO2 = "'" + IDT_EVOLUCAO + "', ";
            }
            string SQL = @"insert into Serix.SRX_BENS_PROPRIEDADES"+_EVOL+
                             @"( " + _CAMPO + @"IDT_BENS_PROP, IDT_PROP, IDT_DESC, QUANT_PROP, ANO_PROP, TPO_VINC, BENS_PROP, DATA_FIM, USER_FIM, QUANT2_PROP, QUANT3_PROP )
						   values
						      ( " + _CAMPO2 + @":IDT_BENS_PROP, :IDT_PROP, :IDT_DESC, :QUANT_PROP, :ANO_PROP, :TPO_VINC, :BENS_PROP, :DATA_FIM, :USER_FIM, :QUANT2_PROP, :QUANT3_PROP )";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_BENS_PROP", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("QUANT_PROP", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("ANO_PROP", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("TPO_VINC", OracleType.Char));
                command.Parameters.Add(new OracleParameter("BENS_PROP", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("DATA_FIM", OracleType.DateTime));
                command.Parameters.Add(new OracleParameter("USER_FIM", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("QUANT2_PROP", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("QUANT3_PROP", OracleType.VarChar));

                command.Parameters["IDT_BENS_PROP"].Value = this.IDT_BENS_PROP;
                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                if (this.QUANT_PROP == "")
                {
                    command.Parameters["QUANT_PROP"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["QUANT_PROP"].Value = this.QUANT_PROP;
                }
                if (this.QUANT2_PROP == "")
                {
                    command.Parameters["QUANT2_PROP"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["QUANT2_PROP"].Value = this.QUANT2_PROP;
                }
                if (this.QUANT3_PROP == "")
                {
                    command.Parameters["QUANT3_PROP"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["QUANT3_PROP"].Value = this.QUANT2_PROP;
                }
                command.Parameters["ANO_PROP"].Value = this.ANO_PROP;
                command.Parameters["TPO_VINC"].Value = this.TPO_VINC;
                command.Parameters["BENS_PROP"].Value = this.BENS_PROP;
                if (this.DATA_FIM == "")
                {
                    command.Parameters["DATA_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["DATA_FIM"].Value = this.DATA_FIM;
                }
                if (this.USER_FIM == "")
                {
                    command.Parameters["USER_FIM"].Value = DBNull.Value;
                }
                else
                {
                    command.Parameters["USER_FIM"].Value = this.USER_FIM;
                }

                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
                command.Connection.Close();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();
                command.Connection.Close();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }

        }

        public void Alterar(string Login, string Alteracao)
        {
            OracleCommand command = new OracleCommand();
            OracleTransaction trans;
            string SQL = "";

            if (Alteracao == "Q")
            {
                SQL = @"update serix.SRX_BENS_PROPRIEDADES set
                            QUANT_PROP = :QUANT_PROP, QUANT2_PROP = :QUANT2_PROP, QUANT3_PROP = :QUANT3_PROP
                            WHERE ANO_PROP = :ANO_PROP AND IDT_DESC = :IDT_DESC AND IDT_PROP = :IDT_PROP";
            }
            else if (Alteracao == "D")
            {
                SQL = @"update serix.SRX_BENS_PROPRIEDADES set
                            DATA_FIM = :DATA_FIM, USER_FIM = :USER_FIM
                            WHERE ANO_PROP = :ANO_PROP AND IDT_DESC = :IDT_DESC AND IDT_PROP = :IDT_PROP";
            }
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo);
                trans = command.Connection.BeginTransaction();
                command.Transaction = trans;
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;


                if (Alteracao == "Q")
                {
                    command.Parameters.Add(new OracleParameter("QUANT_PROP", OracleType.VarChar));
                    command.Parameters.Add(new OracleParameter("QUANT2_PROP", OracleType.VarChar));
                    command.Parameters.Add(new OracleParameter("QUANT3_PROP", OracleType.VarChar));
                    if (this.QUANT_PROP == "")
                    {
                        command.Parameters["QUANT_PROP"].Value = DBNull.Value;
                    }
                    else
                    {
                        command.Parameters["QUANT_PROP"].Value = this.QUANT_PROP;
                    } 
                    if (this.QUANT2_PROP == "")
                    {
                        command.Parameters["QUANT2_PROP"].Value = DBNull.Value;
                    }
                    else
                    {
                        command.Parameters["QUANT2_PROP"].Value = this.QUANT2_PROP;
                    }
                    if (this.QUANT3_PROP == "")
                    {
                        command.Parameters["QUANT3_PROP"].Value = DBNull.Value;
                    }
                    else
                    {
                        command.Parameters["QUANT3_PROP"].Value = this.QUANT3_PROP;
                    }
                }
                else if (Alteracao == "D")
                {
                    command.Parameters.Add(new OracleParameter("DATA_FIM", OracleType.DateTime));
                    command.Parameters.Add(new OracleParameter("USER_FIM", OracleType.VarChar));
                    command.Parameters["DATA_FIM"].Value = DateTime.Now;
                    command.Parameters["USER_FIM"].Value = Login;
                }

                command.Parameters.Add(new OracleParameter("IDT_DESC", OracleType.Number));
                command.Parameters.Add(new OracleParameter("ANO_PROP", OracleType.VarChar));
                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));

                command.Parameters["IDT_PROP"].Value = this.IDT_PROP;
                command.Parameters["IDT_DESC"].Value = this.IDT_DESC;
                command.Parameters["ANO_PROP"].Value = this.ANO_PROP;


                command.ExecuteNonQuery();
                command.Parameters.Clear();
                command.Transaction.Commit();
                command.Connection.Close();
            }
            catch (Exception e)
            {
                command.Transaction.Rollback();
                command.Connection.Close();

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        static public DataTable PesquisarPorEvolucao(string idt, string Login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_BENS_PROPRIEDADES_EVOL WHERE IDT_EVOLUCAO = :IDT";
            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT", OracleType.Number));
                command.Parameters["IDT"].Value = idt;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }
        
        static public DataTable PesquisarPorPropriedade(string Idt_prop, string Login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_BENS_PROPRIEDADES WHERE IDT_PROP = :IDT_PROP AND ANO_PROP = :ANO_PROP AND USER_FIM IS NULL";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;

                command.Parameters.Add(new OracleParameter("IDT_PROP", OracleType.Number));
                command.Parameters["IDT_PROP"].Value = Idt_prop;
                command.Parameters.Add(new OracleParameter("ANO_PROP", OracleType.VarChar));
                command.Parameters["ANO_PROP"].Value = DateTime.Now.Year;

                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;
            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        static public CBensPropriedades PesquisarPorIdt(string Idt, string Login, bool Evolucao)
        {
            CBensPropriedades obj;
            string SQL = "";
            if (Evolucao)
            {
                SQL = @"SELECT *
                            FROM  Serix.SRX_BENS_PROPRIETARIOS_EVOL 
                            WHERE IDT_EVOLUCAO = :IDT";
            }
            else
            {
                SQL = @"SELECT *
                            FROM  Serix.SRX_BENS_PROPRIETARIOS 
                            WHERE IDT_BENS_PROP = :IDT";
            }

            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro("IDT", DbType.String, Idt);

                qy.Executar();
                obj = new CBensPropriedades(qy.ObterCampo("IDT_BENS_PROP"), qy.ObterCampo("IDT_PROP"), qy.ObterCampo("IDT_DESC"), qy.ObterCampo("QUANT_PROP"), qy.ObterCampo("ANO_PROP"), qy.ObterCampo("TPO_VINC"), qy.ObterCampo("BENS_PROP"), qy.ObterCampo("DATA_FIM"), qy.ObterCampo("USER_FIM"), qy.ObterCampo("QUANT2_PROP"), qy.ObterCampo("QUANT3_PROP"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CBensPropriedades();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        static public DataTable ObterTodos(string Login)
        {
            OracleCommand command = new OracleCommand();

            string SQL = @"select * from Serix.SRX_BENS_PROPRIEDADES WHERE ANO_PROP = :ANO_PROP AND USER_FIM IS NULL";

            try
            {
                command.Connection = BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, false);
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("ANO_PROP", OracleType.VarChar));
                command.Parameters["ANO_PROP"].Value = DateTime.Now.Year.ToString();
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;


            }
            catch (Exception e)
            {

                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
        }

        static public string ObterIdt()
        {
            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            string sql = "SELECT COUNT(IDT_BENS_PROP) FROM SERIX.SRX_BENS_PROPRIEDADES";
            Query qy = new Query(con, sql);
            string retorno = "";

            try
            {
                con.Open();
                qy.Executar();
                retorno = Convert.ToString(int.Parse(qy.ObterCampo("COUNT(IDT_BENS_PROP)")) + 1);
            }
            catch
            {

            }
            finally
            {
                con.Close();
            }
            return retorno;
        }

        static public CBensPropriedades PesquisarPorIdtDesc(string idt_desc, string Login)
        {
            CBensPropriedades obj;
            string SQL = @"SELECT *
                            FROM  Serix.SRX_BENS_PROPRIEDADES 
                            WHERE IDT_DESC = :IDT_DESC AND ANO_PROP = :ANO_PROP AND USER_FIM IS NULL";
            Query qy = null;
            try
            {
                qy = new Query(BiblioPMS.ObterConexao(BiblioPMS.aplicacaoSNG, Login, BiblioPMS.Demo), SQL);
                qy.AdicionarParametro("IDT_DESC", DbType.String, idt_desc);
                qy.AdicionarParametro("ANO_PROP", DbType.String, DateTime.Now.Year.ToString());

                qy.Executar();
                obj = new CBensPropriedades(qy.ObterCampo("IDT_BENS_PROP"), qy.ObterCampo("IDT_PROP"), qy.ObterCampo("IDT_DESC"), qy.ObterCampo("QUANT_PROP"), qy.ObterCampo("ANO_PROP"), qy.ObterCampo("TPO_VINC"), qy.ObterCampo("BENS_PROP"), qy.ObterCampo("DATA_FIM"), qy.ObterCampo("USER_FIM"), qy.ObterCampo("QUANT2_PROP"), qy.ObterCampo("QUANT3_PROP"));
            }
            catch
            {
                if (qy.EstadoConexao() == ConnectionState.Open)
                {
                    qy.FecharConexao();
                }
                return new CBensPropriedades();
            }
            finally
            {
                qy.FecharConexao();
            }
            return obj;
        }

        /// <summary>
        /// Metodo para preencher o grid com a descrição e carrega as unidades de medidas
        /// </summary>
        /// <param name="TPO_VINC">Tipo do Vinculo</param>
        /// <returns>Data Tabe para o Grid</returns>
        static public DataTable PreencherGrid(string TPO_VINC, bool SomenteMarcados)
        {
            OracleCommand command = new OracleCommand();
            string SQL;
            OracleConnection con = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
            if (SomenteMarcados)
            {
                SQL = @"SELECT D.IDT_DESC, D.TPO_VINC, D.DESCRICAO, T.DSC_TPO_MED, M.DSC_TPO_MED, S.DSC_TPO_MED FROM SRX_DESCRICOES_OPCOES D 
                            JOIN SRX_BENS_PROPRIEDADES P ON (D.IDT_DESC = P.IDT_DESC)
                            LEFT JOIN SRX_TIPOS_MEDIDAS T ON (D.IDT_TPO_MED = T.IDT_TPO_MED)
                            LEFT JOIN SRX_TIPOS_MEDIDAS M ON (D.IDT_TPO_MED_ = M.IDT_TPO_MED)
                            LEFT JOIN SRX_TIPOS_MEDIDAS S ON (D.IDT_TPO_MED__ = S.IDT_TPO_MED)
                            WHERE (D.TPO_VINC = :TPO_VINC AND P.USER_FIM IS NULL) ORDER BY DESCRICAO";
            }
            else
            {
                SQL = @"SELECT D.IDT_DESC, D.TPO_VINC, D.DESCRICAO, T.DSC_TPO_MED, M.DSC_TPO_MED, S.DSC_TPO_MED FROM SRX_DESCRICOES_OPCOES D 
                            JOIN SRX_TIPOS_MEDIDAS T ON (D.IDT_TPO_MED = T.IDT_TPO_MED)
                            LEFT JOIN SRX_TIPOS_MEDIDAS M ON (D.IDT_TPO_MED_ = M.IDT_TPO_MED)
                            LEFT JOIN SRX_TIPOS_MEDIDAS S ON (D.IDT_TPO_MED__ = S.IDT_TPO_MED)
                            WHERE (D.TPO_VINC = :TPO_VINC) ORDER BY DESCRICAO";
            }

            try
            {
                command.Connection = con;
                con.Open();
                command.CommandType = CommandType.Text;
                command.CommandText = SQL;
                command.Parameters.Add(new OracleParameter("TPO_VINC", OracleType.VarChar));
                command.Parameters["TPO_VINC"].Value = TPO_VINC;
                DataTable tbl = new DataTable();
                tbl.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                return tbl;


            }
            catch (Exception e)
            {
                if (e is OracleException)
                    throw new Exception(Biblio.ObterMensagem(((OracleException)e).Code, e.Message));
                else
                    throw new Exception(e.Message + " : " + "");
            }
            
        }
    }
}
